DESC[RIBE]
Purpose
Use this statement to print column information for a given table or view.
Prerequisites
-
You need either the system privilege
USE ANY SCHEMA
or the object privilegeUSAGE
on the target schema, or the schema must be owned by you or one of your assigned roles. - If the object to be described is a table, one of the following conditions must be fulfilled:
- The current user has one of the following system privileges:
SELECT ANY TABLE
(orSELECT ANY DICTIONARY
in context of system tables, respectively),INSERT ANY TABLE
,UPDATE ANY TABLE
,DELETE ANY TABLE
,ALTER ANY TABLE
orDROP ANY TABLE
. - The current user has any object privilege on the table.
- The table belongs to the current user or one of their roles.
- The current user has one of the following system privileges:
- If the object to be described is a view, one of the following conditions must be fulfilled:
- The current user has one of the following system privileges:
SELECT ANY TABLE
orDROP ANY VIEW
. - The current user has any object privilege on the view.
- The view belongs to the current user or one of their roles.
- The current user has one of the following system privileges:
Syntax
describe::=
Usage notes
- The
SQL_TYPE
column shows the data type. In case of a string type, the used character set (ASCII or UTF-8) is also shown. - The
NULLABLE
column indicates whether the column is permitted to contain NULL values. - The value of columns
DISTRIBUTION_KEY
andPARTITION_KEY
show whether the column is part of the distribution and partition keys (for additional information, see ALTER TABLE (Distribution/Partitioning)). For views these values are always NULL. - If you specify the option
FULL
the additional columnCOLUMN_COMMENT
shows the column comment (cut to maximum 200 characters), provided that this was set either implicitly by the CREATE TABLE or CREATE VIEW command or explicitly by the COMMENT statement. DESCRIBE
can be abbreviated asDESC
. For example:DESC my_table;
.
Example:
CREATE TABLE t (i DECIMAL COMMENT IS 'id column',
d DECIMAL(20,5),
j DATE,
k VARCHAR(5),
DISTRIBUTE BY i,
PARTITION BY d);
DESCRIBE t;
Result:
COLUMN_NAME | SQL_TYPE | NULLABLE | DISTRIBUTION_KEY | PARTITION_KEY |
---|---|---|---|---|
I | DECIMAL(18,0) | TRUE | TRUE | FALSE |
D | DECIMAL(20,5) | TRUE | FALSE | TRUE |
J | DATE | TRUE | FALSE | FALSE |
K | VARCHAR(5) UTF-8 | TRUE | FALSE | FALSE |